package com.css.fxfzypg.modules.test.repository.impl;

import com.css.fxfzypg.modules.test.repository.entity.TestEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * jdbc的例子
 */
@Repository
public class UserRepository {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public List<Map<String,Object>> getUserList(){
        String sql = "select * from `test`";
        List<Map<String, Object>> userList = jdbcTemplate.queryForList(sql);
        return userList;
    }

    //返回vo
    public void queryList(){
        //(1)
        //该方法无法实现类中变量是对象时的转换，例如存在枚举对象。
        //List<StaffUnionVO> result = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(StaffUnionVO.class));
        //(2)
//        可以自定义转化
//        @SuppressWarnings("unchecked")
//        List<StaffUnionVO> result = jdbcTemplate.query(sqlx,  new RowMapper() {
//              @Override
//              public Object mapRow(ResultSet rs, int n)  throws SQLException {
//                  StaffUnionVO vo = new StaffUnionVO();
//                  vo.setId(rs.getString("id"));
//                  return vo;
//              }
//        });
        //(3)
        //自定义映射方式的实现写在了 StaffUnionVO类中
        //List<StaffUnionVO> result = jdbcTemplate.query(sql, new StaffUnionVO());
    }


    public TestEntity getUserById(int id){
        String sql = "select * from `test` where id="+id;
        TestEntity user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(TestEntity.class));

        String sql2 = "select * from test where id = ?";
        //新建MyRowMapper类实现RowMapper接口，重写mapRow方法，指定返回User对象
        TestEntity test = jdbcTemplate.queryForObject(sql2, new MyRowMapper(), "李四");
        return user;
    }

    public String addTest(TestEntity user){
        String sql = "insert into `test`(id,name,age,mark) values(?,?,?,?)";

        Object[] objects = new Object[4];
        objects[0] = user.getId();
        objects[1] = user.getName();
        objects[2] = user.getAge();
        objects[3] = user.getMark();

        jdbcTemplate.update(sql,objects);
        return "add ok";
    }

    public String updateUser(TestEntity user){
        String sql = "update `test` set name=?,age=?,mark=? where id=?";

        Object[] objects = new Object[4];
        objects[0] = user.getName();
        objects[1] = user.getAge();
        objects[2] = user.getMark();
        objects[3] = user.getId();

        jdbcTemplate.update(sql,objects);
        return "update ok";
    }

    public String deleteUserById(int id){
        String sql = "delete from `user` where id="+id;
        jdbcTemplate.update(sql);
        return "delete ok";
    }

    /**
     * 使用JDBC底层实现查询
     */
    public static void queryWithJDBC() {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/fwjg";

        try {
//            加载驱动
            Class.forName("com.mysql.jdbc.Driver");
//            创建连接
            conn = DriverManager.getConnection(jdbcUrl, "root", "root");
            String sql = "select * from org_user where user_name = ?";
//            预编译sql
            psmt = conn.prepareStatement(sql);
//            从1开始，没有就不需要
            psmt.setString(1, "jgAdmin");
//            执行sql
            rs = psmt.executeQuery();
//            int num = psmt.executeUpdate(); //增删改，返回操作记录数

//            遍历结果集
            while (rs.next()) {
                //根据列名查询对应的值，也可以是位置序号
                String name = rs.getString("user_name");
                String code = rs.getString("user_code");
                System.out.println(name);
                System.out.println(code);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                psmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}
